/*
 单例模式
 */
#import "DBManager.h"

static DBManager *dbMgr;

@implementation DBManager
/**
 创建数据库
 */
-(BOOL)createDataBaseWithName:(NSString *)dbName{
    NSArray *dirPaths;
    NSString *docsDir;
    
    //获取应用的document目录
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    //拼取数据库地址
    dbName = [NSString stringWithFormat:@"%@.db",dbName];
    dataBasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:dbName]];
    NSLog(@"<SQL>: %@",dataBasePath);
    
    //获取文件管理类
    NSFileManager *filemanager = [NSFileManager defaultManager];
    //当数据库db文件不存在
    if ([filemanager fileExistsAtPath:dataBasePath] == NO) {  //没有创建数据库
        const char *dbpath = [dataBasePath UTF8String];   //数据库地址转UTF－8编码
        if (sqlite3_open(dbpath, &sqliteDataBase)==SQLITE_OK) {
            return YES; //数据库创建成功
        }
        else {
            return NO;  //数据库创建失败
        }
    }
    return YES;
}

//创建table表单
-(BOOL)createTable:(NSString *)sqlString{
    char *errmsg;
    const char *str = [sqlString UTF8String]; //sql语句转化成UTF8编码
    NSLog(@"<SQL>: %s",str);
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) { //打开数据库
        if (sqlite3_exec(sqliteDataBase, str, NULL, NULL, &errmsg)!=SQLITE_OK) {
            sqlite3_close(sqliteDataBase);
            return NO;
        }
    }
    sqlite3_close(sqliteDataBase);
    return true;
}

//删除table表
-(BOOL)dropTable:(NSString *)tableName{
    char *errmsg;
    //const char *createsql = "CREATE TABLE IF NOT EXISTS INFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, NUM TEXT, CLASSNAME TEXT,NAME TEXT)";
    NSString *drop_str = [NSString stringWithFormat:@"drop table %@",tableName];
    NSLog(@"<SQL>: %@", drop_str);
    const char *dropStr = [drop_str UTF8String];
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) { //打开数据库
        if (sqlite3_exec(sqliteDataBase, dropStr, NULL, NULL, &errmsg)!=SQLITE_OK) {
            sqlite3_close(sqliteDataBase);
            return true;
        }
    }
    sqlite3_close(sqliteDataBase);
    return false;
}



/**
 增
 value:
 INSERT INTO myclassinfo(id,stunum,schoolid,name,grade,code) VALUES("1","3","1","dddd","1","dddd")
 */
-(BOOL)insertTo:(NSString *)dbName dictionary:(NSDictionary *) insert_dict {
    NSString *str1 = @"";
    NSString *str2 = @"";
    for (id key in insert_dict) {
        str1 = [str1 stringByAppendingFormat:@",%@",key];
        str2 = [str2 stringByAppendingFormat:@",\"%@\"",[insert_dict objectForKey:key]];
    }
    str1 = [str1 substringFromIndex:1];
    str2 = [str2 substringFromIndex:1];
    
    NSString *sqlString = [NSString stringWithFormat: @"INSERT INTO %@(%@) VALUES(%@)",dbName,str1, str2];
    NSLog(@"<SQL>: %@",sqlString);
    const char *insert_sql = [sqlString UTF8String];
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) { //打开数据库
        char *errorMsg;
        if (sqlite3_exec(sqliteDataBase, insert_sql, NULL, NULL, &errorMsg)==SQLITE_OK) {  //执行
            sqlite3_close(sqliteDataBase);
            return true;
        }else{
            sqlite3_close(sqliteDataBase);
            return false;
        }
    }
    sqlite3_close(sqliteDataBase);
    return false;
}

//删
-(BOOL)deleteFrom:(NSString *)dbName limits:(NSString *)limitString{
    NSString *sqlString = [NSString stringWithFormat: @"DELETE FROM %@ WHERE %@",dbName,limitString];
    NSLog(@"<SQL>: %@",sqlString);
    const char *delete_sql = [sqlString UTF8String];
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) {//打开数据库
        char *errorMsg;
        if (sqlite3_exec(sqliteDataBase, delete_sql, NULL, NULL, &errorMsg)==SQLITE_OK) {//执行
            sqlite3_close(sqliteDataBase);
            return true;
        }else{
            sqlite3_close(sqliteDataBase);
            return false;
        }
    }
    return false;
}

//改
-(BOOL)updateTable:(NSString *)dbName updateDict:(NSDictionary*)updateDict limits:(NSString *)limitString{
    NSString *updateString = @"";
    for (id key in updateDict) {
        updateString = [updateString stringByAppendingFormat:@",%@ = \'%@\'", key,[updateDict objectForKey:key]];
    }
    updateString = [updateString substringFromIndex:1];
    NSString *sqlString = [NSString stringWithFormat: @"UPDATE %@ SET %@ WHERE %@",dbName,updateString, limitString];
    NSLog(@"<SQL>: %@",sqlString);
    const char *update_sql = [sqlString UTF8String];
    const char *dbPath = [dataBasePath UTF8String];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) {//打开数据库
        char *errorMsg;
        if (sqlite3_exec(sqliteDataBase, update_sql, NULL, NULL, &errorMsg)==SQLITE_OK) {//执行
            sqlite3_close(sqliteDataBase);
            return true;
        }else{
            sqlite3_close(sqliteDataBase);
            return false;
        }
    }
    return false;
}

//查
/**
 limitString: age = 13 and name = "joci"
 */
-(NSMutableArray *)selectFrom:(NSString *)tableName items:(NSArray *)itemArray limits:(NSString *)limitString{
    const char *dbPath = [dataBasePath UTF8String];
    sqlite3_stmt *statement;
    
    NSString *itemString = @"";
    for (NSString* temp in itemArray) {
        NSString *str = [NSString stringWithFormat:@",%@",temp];
        itemString = [itemString stringByAppendingString:str];
    }
    itemString = [itemString substringFromIndex:1];
    
    NSMutableArray *resultArr = [[NSMutableArray alloc]init];
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) {
        NSString *querySQL = [NSString stringWithFormat:@"SELECT %@ from %@ where %@",itemString,tableName,limitString];
        NSLog(@"<SQL>: %@", querySQL);
        const char *query_stmt = [querySQL UTF8String];
        if (sqlite3_prepare_v2(sqliteDataBase, query_stmt, -1, &statement, NULL)==SQLITE_OK) {
            while (sqlite3_step(statement)==SQLITE_ROW) {
                NSMutableArray *arr = [[NSMutableArray alloc]init];
                for (int i = 0; i < itemArray.count; i++) {
                    NSString *result = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(statement, i)];
                    [arr addObject:result];
                }
                [resultArr addObject:arr];
            }
        }
    }
    sqlite3_finalize(statement);
    sqlite3_close(sqliteDataBase);
    return resultArr;
}

-(BOOL)isTableExist:(NSString *)tableName{
    const char *dbPath = [dataBasePath UTF8String];
    sqlite3_stmt *statement;
    NSString *querySQL = [NSString stringWithFormat:@"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%@'", tableName];
    const char *select_sql = [querySQL UTF8String];
    NSLog(@"<SQL>: %@",querySQL);
    
    if (sqlite3_open(dbPath, &sqliteDataBase)==SQLITE_OK) {  //打开数据库
        if (sqlite3_prepare_v2(sqliteDataBase, select_sql, -1, &statement, NULL)==SQLITE_OK) {
            while (sqlite3_step(statement)==SQLITE_ROW) {
                NSString *result = [[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(statement, 0)];
                if ([result isEqualToString:@"1"]) {  //存在一张表
                    NSLog(@"Table exist.");
                    return true;
                }
                NSLog(@"Table ! exist.");
                return false;
            }
        }
    }
     NSLog(@"Table ! exist.");
    return false;
}

+(DBManager*)getInstanse{
    if (dbMgr == nil) {
        dbMgr = [[DBManager alloc]init];
    }
    return dbMgr;
}

@end
